Postgresql Notes
Essential Commands
To connect
psql -h <remote_ip> -p <port> -U <username> -d <database_name>
# Example
psql -h localhost -p 5432 -U postgres -d postgres
Restore Database
Loading database from sql file or tar file.
Refer to Neon documentation for more on pgadmin4
Restore from sql file or tar file
psql -f demo-big-en/demo-big-en-20170815.sql -U postgres -h localhost -p 5432 -d demo
pg_restore tar database
# With pg_restore
# Create destination database
CREATE DATABASE dvdrental
pg_restore -U postgres -d dvdrental path/to/dvdrental.tar
Sample Databases
Airlines Data by PostgresPro DVD Rental (Pagila) Northwind
Structure of sql query
SELECT
FROM
WHERE
GROUP BY
HAVING
WINDOW
ORDER BY
LIMIT / OFFSET (or FETCH)
Logical execution order (how PostgreSQL actually processes it)
Even though you write clauses in the order above, PostgreSQL evaluates them roughly like this:
- FROM – build the source rows (joins, subqueries, etc.)
- WHERE – filter individual rows
- GROUP BY – form groups
- HAVING – filter groups
- SELECT – compute output columns/expressions
- WINDOW – apply window functions
- ORDER BY – sort result
- LIMIT / OFFSET – trim final rows Logical execution order (how PostgreSQL actually processes it)
Even though you write clauses in the order above, PostgreSQL evaluates them roughly like this:
- FROM – build the source rows (joins, subqueries, etc.)
- WHERE – filter individual rows
- GROUP BY – form groups
- HAVING – filter groups
- SELECT – compute output columns/expressions
- WINDOW – apply window functions
- ORDER BY – sort result
- LIMIT / OFFSET – trim final rows
--Write order
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
--Think order
FROM → WHERE → GROUP → HAVING → SELECT → ORDER → LIMIT
